#!/usr/bin/env python
# coding:utf-8


import logging
from copy import deepcopy
from utils.commontools import convert_to_date
from utils.mysqlutils import MysqlUtils
from datatabletransfer import DataTableTransfer
from fieldsmap import *


class FundShareChange(DataTableTransfer):
    """
    基金份额(TQ_FD_SHARE)	--> 基金份额变动(fund_share_change)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,REPORTDATE as end_date,REPORTTYPE as report_type from TQ_FD_SHARE  ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date, 
        report_type from fund_share_change f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID, DECLAREDATE,REPORTDATE,REPORTTYPE,CHGRSN,INIFDSHARE,
    # SUBSHARETOT,REDTOTSHARE,SPLITADDSHARE,ENDFDSHARE,NCIRCORIGSHARE,CIRCORIGSHARE,CIRCSHARE,PUBLICSHARE,INSTINVESHARE,
    # ISSTAT,ISVALID,REMARK from TQ_FD_SHARE limit %s, 1000;''')

    TABLE = 'fund_share_change'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'report_type':True}    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundShareChangeMap
    MAP_VALUES = FundShareChangeValueMap


class FundHolderInfo(DataTableTransfer):
    """
    基金份额持有信息统计(TQ_FD_SHARESTAT) --> 基金份额持有人信息(fund_holder_info)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date from TQ_FD_SHARESTAT;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date 
           from fund_holder_info f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,ENDDATE,HOLDERNUM,AVGSHARE,INVSHARE,
    # INVTOTRTO,INDIVIDUALSHARE,INDIVIDUALTOTRTO,KEEPERSHARE,KEEPERTOTRTO,SHHOLDERSHARE,SHHOLDERTOTRTO,STAFFSHARE,
    # STAFFTOTRTO,UNCONFSHARE,UNCONFTOTRTO,ISSTAT,MEMO from TQ_FD_SHARESTAT limit %s, 1000;''')

    TABLE = 'fund_holder_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundHolderInfoMap
    MAP_VALUES = FundHolderInfoValueMap


class FundTopTenHolder(DataTableTransfer):
    """
    基金十大持有人(TQ_FD_HOLDERTOP10) --> 基金前十名持有人(fund_top_ten_holder)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,HOLDERRANK as  holder_serial_number,
    HOLDERNAME as holder_name from TQ_FD_HOLDERTOP10;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date ,
    holder_serial_number,holder_name  from fund_top_ten_holder f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code 
    where  f.status<>'9';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,ENDDATE,HOLDERRANK,HOLDERTYPE,
    # HOLDERNAME,concat('FD',HOLDERCODE) as HOLDERCODE,HOLDSHARE,HOLDRTO from TQ_FD_HOLDERTOP10 limit %s, 1000;''')

    TABLE = 'fund_top_ten_holder'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'holder_serial_number':False, 'holder_name':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundTopTenHolderMap
    MAP_VALUES = FundTopTenHolderValueMap

    @classmethod
    def upset(cls, table, row, column_value, upset):
        """
        更新记录: holder_code取值范围
        :param table:
        :param row:
        :param column_value:
        :param upset:
        :return:
        """

        item = deepcopy(cls.MAP_FIELDS)
        for key, value in item.iteritems():
            # 判断是否存在值匹配
            if value and cls.MAP_VALUES.has_key(key):
                map_value = cls.MAP_VALUES[key]
                if map_value.keys()[0] == 'SQL':   # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]
                    item[key] = cls.exec_sql(conf, query, (row[value], ), default_value)
                else:
                    item[key] = map_value.get(row[value], 0)
            # 如果value存在，进行匹配
            elif value:
                item[key] = row[value] if row[value] or row[value] == 0 else None

        # 修正默认值
        for key, value in cls.DEFAULT_VALUE.iteritems():
            if not item[key]:
                item[key] = value

        # 修正日期时间型
        for key, value in cls.DATETIME.iteritems():
            if item.has_key(key) and len(str(item[key])) == 8 and item[key] != '19000101':
                item[key] = convert_to_date('{0}-{1}-{2} 00:00:00'
                                            .format(item[key][:4], item[key][4:6], item[key][6:8]))
            else:
                item[key] = None

        # holder_code取值
        item['holder_code'] = cls.get_holder_code(item['holder_type'], item['holder_code'])
        cls.write_mysql(table, item, column_value, upset)

    @classmethod
    def get_holder_code(cls, holder_type, holder_code):
        """
        处理holder_code取值
        :param holder_type: 2=人物，1=机构
        :param holder_code:
        :return:
        """

        if not holder_code:
            return None

        org_query = '''select com_uni_code from org_basic_info where source_id=%s and status!='9' limit 1;'''
        peo_query = '''select peo_uni_code from peo_basic_info where source_id=%s and status!='9' limit 1;'''
        if holder_type == '2':
            po_uni_code = cls.exec_sql(conf='data_center', sql=peo_query, params=(holder_code,))
        else:
            po_uni_code = cls.exec_sql(conf='data_center', sql=org_query, params=(holder_code,))
        return po_uni_code


class FundTradingShare(DataTableTransfer):
    """
    上市型基金每日份额表(TQ_FD_EXCHSHARE) --> 上市基金每日份额(fund_listed_trading_share)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,TRADEDATE as trade_date  from TQ_FD_EXCHSHARE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.trade_date,'-','') as trade_date
           from fund_listed_trading_share f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,TRADEDATE,CURSHARE
    # from TQ_FD_EXCHSHARE limit %s, 1000;''')

    TABLE = 'fund_listed_trading_share'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'trade_date':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'trade_date': None,
    }

    MAP_FIELDS = FundTradingShareMap
    MAP_VALUES = FundTradingShareValueMap


class FundMangerInfo(DataTableTransfer):
    """
    基金公司高管资料表(TQ_FD_COMPMGINFO) --> 基金公司管理层(fund_com_management_info)
    """
    QUERY_SOURCE = '''select  COMPCODE as org_uni_code,BEGINDATE as begin_date,PSNAME as name,POST as post from TQ_FD_COMPMGINFO;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as org_uni_code, replace(f.begin_date,'-','') as begin_date,name,post
    from fund_com_management_info f join org_basic_info b on f.org_uni_code = b.com_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',COMPCODE) as COMPCODE,DECLAREADATE,BEGINDATE,ENDDATE,
    # concat('FD', PSCODE) as PSCODE,PSNAME,POST,GENDER,BIRTHDATE,DEGREE,CV,DATASOURCE,MEMO
    # from TQ_FD_COMPMGINFO limit %s, 1000;''')

    TABLE = 'fund_com_management_info'  # 待插入的表名
    SOURCE_ID = {'org_uni_code':False, 'begin_date':False, 'name':False, 'post':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None
    }

    MAP_FIELDS = FundMangerInfoMap
    MAP_VALUES = FundMangerInfoValueMap

    @classmethod
    def write_mysql(cls, table, item, column_value, upset):
        """
        更新数据写入MySQL
        :param table:
        :param item:
        :param column_value:
        :param upset:
        :return:
        """

        # 不能存org_uni_code=0的记录
        if item.has_key('org_uni_code') and item['org_uni_code'] == 0:
            return

        conn = MysqlUtils.connect(cls.data_center)
        if upset and column_value:
            cursor = MysqlUtils.columns_update(conn=conn, table=table,
                                               columns=tuple(item.keys()), values=tuple(item.values()),
                                               select_column=cls.COLUMN_ID, select_value=column_value)
        else:
            cursor = MysqlUtils.columns_insert(conn=conn, table=table,
                                               columns=tuple(item.keys()), values=tuple(item.values()))
        if cursor:
            cursor.close()
        conn.close()


class FundHolderMeeting(DataTableTransfer):
    """
    基金持有人大会应用表(TQ_FD_HMEETINGAPP) --> 基金持有人大会(fund_holder_meeting)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,DECLAREDATE as announcement_date, MEETINGTYPE as meeting_type from TQ_FD_HMEETINGAPP;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.announcement_date,'-','') as announcement_date,
     meeting_type  from fund_holder_meeting f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID,DECLAREDATE,UPDATEDATE,MEETINGTYPE,YEARS,NUMBER,
    # CONVENINGWAY,CONVENINGPLACE,THEME,DECISSION,ANNOUNCEDATE,RECORDDATE,PREREGBEG,PREREGEND,CONVENINGBEG,CONVENINGEND,
    # RESOLDATE,CONTINUSUSPBEG,CONTINUSUSPEND,MEMO from TQ_FD_HMEETINGAPP limit %s, 1000;''')

    TABLE = 'fund_holder_meeting'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'announcement_date':False, 'meeting_type':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'update_date': None,
        'convening_announcement_date': None,
        'record_date': None,
        'pre_record_beg_date': None,
        'pre_record_end_date': None,
        'convening_beg_date': None,
        'convening_end_date': None,
        'decision_announcement_date': None,
        'continuous_suspend_beg_date': None,
        'continuous_suspend_end_date': None,
    }

    MAP_FIELDS = FundHolderMeetingMap
    MAP_VALUES = FundHolderMeetingValueMap


class FundSIHShare(DataTableTransfer):
    """
    投资者持有超过20%份额基金(TQ_FD_FUNDSISM) --> 单一投资者持有基金份额(fund_single_investor_hold_share)
    """
    QUERY_SOURCE = '''select  b.SECURITYID as sec_uni_code,a.REPORTTYPE as report_type ,a.INVESTORTYPE as investor_type ,a.RN as serial_number,
    a.BEGINDATE as share_exs_20pct_beg_date, a.ENDDATE as share_exs_20pct_end_date from TQ_FD_FUNDSISM a, TQ_OA_STCODE b where a.SECODE=b.SECODE;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,report_type,investor_type,serial_number,
    replace(f.share_exs_20pct_beg_date,'-','') as share_exs_20pct_beg_date, replace(f.share_exs_20pct_end_date,'-','') as share_exs_20pct_end_date
    from fund_single_investor_hold_share f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', b.SECURITYID) SECURITYID,a.DECLAREDATE,a.REPORTTYPE,a.INVESTORTYPE,a.RN,
    # a.BEGINDATE,a.ENDDATE,a.INITIALAMT,a.BSHARE,a.RSHARE,a.FINHOLDSHARE,a.FINSHARERATIO
    # from TQ_FD_FUNDSISM a, TQ_OA_STCODE b where a.SECODE=b.SECODE limit %s, 1000;''')

    TABLE = 'fund_single_investor_hold_share'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'report_type':True, 'investor_type':True, 'serial_number':False, 'share_exs_20pct_beg_date':False,
                 'share_exs_20pct_end_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'share_exs_20pct_beg_date': None,
        'share_exs_20pct_end_date': None,
    }
    IDX = ('sec_uni_code', 'report_type', 'investor_type', 'serial_number')
    MAP_FIELDS = FundSIHShareMap
    MAP_VALUES = FundSIHShareValueMap


if __name__ == '__main__':

    log_path = '../logs/export_fund_holders_server.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # FundShareChange.work()
    # FundHolderInfo.work()
    # FundTopTenHolder.work()
    # FundTradingShare.work()
    # FundMangerInfo.work()
    # FundHolderMeeting.work()
    FundSIHShare.work()
